

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO

ALTER PROCEDURE RethrowError AS
    /* Return if there is no error information to retrieve. */
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200); 

    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 

    /* Building the message string that will contain original
       error information. */

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
         'Message: '+ ERROR_MESSAGE(); 

    /* Raise an error: msg_str parameter of RAISERROR will contain
	   the original error information. */

    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
        @ErrorNumber,    /* parameter: original error number. */
        @ErrorSeverity,  /* parameter: original error severity. */
        @ErrorState,     /* parameter: original error state. */
        @ErrorProcedure, /* parameter: original error procedure name. */
        @ErrorLine       /* parameter: original error line number. */
        );

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Find')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Find] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Find] 
    @Table varchar(50),
    @Where varchar(200)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    if(@Where = '')
		Exec ('Select * from '+ @Table )
	else
		Exec ('Select * from '+ @Table +' Where '+@Where)
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'FindCount')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[FindCount] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[FindCount] 
    @Table varchar(50),
    @Where varchar(200)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    if(@Where = '')
		Exec ('SELECT Count(*) FROM '+ @Table )
	else
		Exec ('SELECT Count(*) FROM '+ @Table +' Where '+@Where)
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteWithCondition')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteWithCondition] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteWithCondition] 
    @Table varchar(50),
    @Where varchar(200)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    if(@Where = '')
		Exec ('DELETE FROM '+ @Table )
	else
		Exec ('DELETE FROM '+ @Table +' Where '+@Where)
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

----------------------------------------------------------------
-- [dbo].[_Procedures] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Procedures_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Procedures_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Procedures_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[_Procedures].[idProcedure] AS 'idProcedure',
	[_Procedures].[Name] AS 'Name'
FROM [dbo].[_Procedures] [_Procedures]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Procedures_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Procedures_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Procedures_GetById] 
	@idProcedure int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[_Procedures].[idProcedure] AS 'idProcedure',
	[_Procedures].[Name] AS 'Name'
	FROM [dbo].[_Procedures] [_Procedures]
	WHERE [idProcedure]=@idProcedure

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Procedures_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Procedures_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Procedures_Insert]
    @idProcedure int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[_Procedures] ([Name])
	VALUES (@name)
    SET @idProcedure = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Procedures_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Procedures_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Procedures_Update]
    @idProcedure int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[_Procedures] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[_Procedures] 
	SET [Name] = @name
	WHERE [idProcedure]=@idProcedure

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Procedures_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Procedures_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Procedures_Delete]
	 @idProcedure int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[_Procedures]
	WHERE [idProcedure]=@idProcedure
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[_Status] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Status_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Status_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Status_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[_Status].[idStatus] AS 'idStatus',
	[_Status].[Name] AS 'Name'
FROM [dbo].[_Status] [_Status]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Status_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Status_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Status_GetById] 
	@idStatus int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[_Status].[idStatus] AS 'idStatus',
	[_Status].[Name] AS 'Name'
	FROM [dbo].[_Status] [_Status]
	WHERE [idStatus]=@idStatus

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Status_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Status_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Status_Insert]
    @idStatus int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[_Status] ([Name])
	VALUES (@name)
    SET @idStatus = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Status_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Status_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Status_Update]
    @idStatus int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[_Status] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[_Status] 
	SET [Name] = @name
	WHERE [idStatus]=@idStatus

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = '_Status_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[_Status_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[_Status_Delete]
	 @idStatus int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[_Status]
	WHERE [idStatus]=@idStatus
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[AdministrationVias] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AdministrationVias_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[AdministrationVias_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[AdministrationVias_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[administrationVias].[idAdministrationVia] AS 'idAdministrationVia',
	[administrationVias].[Name] AS 'Name'
FROM [dbo].[AdministrationVias] [administrationVias]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AdministrationVias_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[AdministrationVias_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[AdministrationVias_GetById] 
	@idAdministrationVia int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[administrationVias].[idAdministrationVia] AS 'idAdministrationVia',
	[administrationVias].[Name] AS 'Name'
	FROM [dbo].[AdministrationVias] [administrationVias]
	WHERE [idAdministrationVia]=@idAdministrationVia

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AdministrationVias_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[AdministrationVias_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[AdministrationVias_Insert]
    @idAdministrationVia int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[AdministrationVias] ([Name])
	VALUES (@name)
    SET @idAdministrationVia = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AdministrationVias_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[AdministrationVias_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[AdministrationVias_Update]
    @idAdministrationVia int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[AdministrationVias] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[AdministrationVias] 
	SET [Name] = @name
	WHERE [idAdministrationVia]=@idAdministrationVia

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'AdministrationVias_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[AdministrationVias_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[AdministrationVias_Delete]
	 @idAdministrationVia int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[AdministrationVias]
	WHERE [idAdministrationVia]=@idAdministrationVia
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Allergies] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Allergies_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Allergies_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Allergies_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[allergies].[idAllergie] AS 'idAllergie',
	[allergies].[Name] AS 'Name'
FROM [dbo].[Allergies] [allergies]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Allergies_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Allergies_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Allergies_GetById] 
	@idAllergie int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[allergies].[idAllergie] AS 'idAllergie',
	[allergies].[Name] AS 'Name'
	FROM [dbo].[Allergies] [allergies]
	WHERE [idAllergie]=@idAllergie

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Allergies_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Allergies_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Allergies_Insert]
    @idAllergie int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Allergies] ([Name])
	VALUES (@name)
    SET @idAllergie = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Allergies_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Allergies_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Allergies_Update]
    @idAllergie int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[Allergies] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Allergies] 
	SET [Name] = @name
	WHERE [idAllergie]=@idAllergie

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Allergies_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Allergies_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Allergies_Delete]
	 @idAllergie int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Allergies]
	WHERE [idAllergie]=@idAllergie
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[BloodTypes] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'BloodTypes_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[BloodTypes_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[BloodTypes_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[bloodTypes].[idBloodType] AS 'idBloodType',
	[bloodTypes].[Name] AS 'Name'
FROM [dbo].[BloodTypes] [bloodTypes]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'BloodTypes_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[BloodTypes_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[BloodTypes_GetById] 
	@idBloodType int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[bloodTypes].[idBloodType] AS 'idBloodType',
	[bloodTypes].[Name] AS 'Name'
	FROM [dbo].[BloodTypes] [bloodTypes]
	WHERE [idBloodType]=@idBloodType

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'BloodTypes_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[BloodTypes_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[BloodTypes_Insert]
    @idBloodType int OUT,
	@name char(20) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[BloodTypes] ([Name])
	VALUES (@name)
    SET @idBloodType = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'BloodTypes_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[BloodTypes_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[BloodTypes_Update]
    @idBloodType int,
	@name char(20) = NULL
AS
BEGIN

	--The [dbo].[BloodTypes] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[BloodTypes] 
	SET [Name] = @name
	WHERE [idBloodType]=@idBloodType

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'BloodTypes_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[BloodTypes_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[BloodTypes_Delete]
	 @idBloodType int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[BloodTypes]
	WHERE [idBloodType]=@idBloodType
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Conditions] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Conditions_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Conditions_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Conditions_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[conditions].[idConditions] AS 'idConditions',
	[conditions].[Name] AS 'Name'
FROM [dbo].[Conditions] [conditions]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Conditions_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Conditions_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Conditions_GetById] 
	@idConditions int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[conditions].[idConditions] AS 'idConditions',
	[conditions].[Name] AS 'Name'
	FROM [dbo].[Conditions] [conditions]
	WHERE [idConditions]=@idConditions

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Conditions_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Conditions_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Conditions_Insert]
    @idConditions int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Conditions] ([Name])
	VALUES (@name)
    SET @idConditions = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Conditions_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Conditions_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Conditions_Update]
    @idConditions int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[Conditions] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Conditions] 
	SET [Name] = @name
	WHERE [idConditions]=@idConditions

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Conditions_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Conditions_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Conditions_Delete]
	 @idConditions int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Conditions]
	WHERE [idConditions]=@idConditions
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[ContactData] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'ContactData_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[ContactData_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[ContactData_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[contactData].[idContactData] AS 'idContactData',
	[contactData].[Cel] AS 'Cel',
	[contactData].[Direction] AS 'Direction',
	[contactData].[FirstName] AS 'FirstName',
	[contactData].[LasName2] AS 'LasName2',
	[contactData].[LastName] AS 'LastName',
	[contactData].[Mail] AS 'Mail',
	[contactData].[MiddleName] AS 'MiddleName',
	[contactData].[Tel] AS 'Tel'
FROM [dbo].[ContactData] [contactData]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'ContactData_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[ContactData_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[ContactData_GetById] 
	@idContactData int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[contactData].[idContactData] AS 'idContactData',
	[contactData].[Cel] AS 'Cel',
	[contactData].[Direction] AS 'Direction',
	[contactData].[FirstName] AS 'FirstName',
	[contactData].[LasName2] AS 'LasName2',
	[contactData].[LastName] AS 'LastName',
	[contactData].[Mail] AS 'Mail',
	[contactData].[MiddleName] AS 'MiddleName',
	[contactData].[Tel] AS 'Tel'
	FROM [dbo].[ContactData] [contactData]
	WHERE [idContactData]=@idContactData

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'ContactData_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[ContactData_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[ContactData_Insert]
    @idContactData int OUT,
	@cel char(18) = NULL,
	@direction varchar(255) = NULL,
	@firstName char(15),
	@lasName2 char(15) = NULL,
	@lastName char(15) = NULL,
	@mail varchar(100) = NULL,
	@middleName char(15) = NULL,
	@tel char(18) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[ContactData] ([Cel], [Direction], [FirstName], [LasName2], [LastName], [Mail], [MiddleName], [Tel])
	VALUES (@cel, @direction, @firstName, @lasName2, @lastName, @mail, @middleName, @tel)
    SET @idContactData = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'ContactData_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[ContactData_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[ContactData_Update]
    @idContactData int,
	@cel char(18) = NULL,
	@direction varchar(255) = NULL,
	@firstName char(15),
	@lasName2 char(15) = NULL,
	@lastName char(15) = NULL,
	@mail varchar(100) = NULL,
	@middleName char(15) = NULL,
	@tel char(18) = NULL
AS
BEGIN

	--The [dbo].[ContactData] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[ContactData] 
	SET [Cel] = @cel, [Direction] = @direction, [FirstName] = @firstName, [LasName2] = @lasName2, [LastName] = @lastName, [Mail] = @mail, [MiddleName] = @middleName, [Tel] = @tel
	WHERE [idContactData]=@idContactData

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'ContactData_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[ContactData_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[ContactData_Delete]
	 @idContactData int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[ContactData]
	WHERE [idContactData]=@idContactData
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Inmunizations] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Inmunizations_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Inmunizations_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Inmunizations_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[inmunizations].[idInmunization] AS 'idInmunization',
	[inmunizations].[Name] AS 'Name'
FROM [dbo].[Inmunizations] [inmunizations]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Inmunizations_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Inmunizations_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Inmunizations_GetById] 
	@idInmunization int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[inmunizations].[idInmunization] AS 'idInmunization',
	[inmunizations].[Name] AS 'Name'
	FROM [dbo].[Inmunizations] [inmunizations]
	WHERE [idInmunization]=@idInmunization

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Inmunizations_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Inmunizations_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Inmunizations_Insert]
    @idInmunization int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Inmunizations] ([Name])
	VALUES (@name)
    SET @idInmunization = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Inmunizations_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Inmunizations_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Inmunizations_Update]
    @idInmunization int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[Inmunizations] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Inmunizations] 
	SET [Name] = @name
	WHERE [idInmunization]=@idInmunization

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Inmunizations_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Inmunizations_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Inmunizations_Delete]
	 @idInmunization int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Inmunizations]
	WHERE [idInmunization]=@idInmunization
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Medications] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Medications_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[medications].[idMedication] AS 'idMedication',
	[medications].[AdminVia] AS 'AdminVia',
	[medications].[Name] AS 'Name'
FROM [dbo].[Medications] [medications]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Medications_GetById] 
	@idMedication int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[medications].[idMedication] AS 'idMedication',
	[medications].[AdminVia] AS 'AdminVia',
	[medications].[Name] AS 'Name'
	FROM [dbo].[Medications] [medications]
	WHERE [idMedication]=@idMedication

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_GetByAdministrationVias')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_GetByAdministrationVias] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Medications_GetByAdministrationVias]
@adminVia int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[medications].[idMedication] AS 'idMedication',
	[medications].[AdminVia] AS 'AdminVia',
	[medications].[Name] AS 'Name'
	FROM [dbo].[Medications] [medications]
	WHERE [AdminVia]=@adminVia
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Medications_Insert]
    @idMedication int OUT,
	@adminVia int,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Medications] ([AdminVia], [Name])
	VALUES (@adminVia, @name)
    SET @idMedication = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Medications_Update]
    @idMedication int,
	@adminVia int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[Medications] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Medications] 
	SET [AdminVia] = @adminVia, [Name] = @name
	WHERE [idMedication]=@idMedication

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Medications_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Medications_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Medications_Delete]
	 @idMedication int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Medications]
	WHERE [idMedication]=@idMedication
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Patients] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Patients_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[patients].[idPatient] AS 'idPatient',
	[patients].[idUser] AS 'idUser'
FROM [dbo].[Patients] [patients]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Patients_GetById] 
	@idPatient int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[patients].[idPatient] AS 'idPatient',
	[patients].[idUser] AS 'idUser'
	FROM [dbo].[Patients] [patients]
	WHERE [idPatient]=@idPatient

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_GetByUsers')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_GetByUsers] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Patients_GetByUsers]
@idUser int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[patients].[idPatient] AS 'idPatient',
	[patients].[idUser] AS 'idUser'
	FROM [dbo].[Patients] [patients]
	WHERE [idUser]=@idUser
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Patients_Insert]
    @idPatient int OUT,
	@idUser int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Patients] ([idUser])
	VALUES (@idUser)
    SET @idPatient = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Patients_Update]
    @idPatient int,
	@idUser int
AS
BEGIN

	--The [dbo].[Patients] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Patients] 
	SET [idUser] = @idUser
	WHERE [idPatient]=@idPatient

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Patients_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Patients_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Patients_Delete]
	 @idPatient int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Patients]
	WHERE [idPatient]=@idPatient
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonAllergies] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonAllergies_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
FROM [dbo].[PersonAllergies] [personAllergies]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonAllergies_GetById] 
	@idPersonAllergie int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
	FROM [dbo].[PersonAllergies] [personAllergies]
	WHERE [idPersonAllergie]=@idPersonAllergie

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetByAllergies')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetByAllergies] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonAllergies_GetByAllergies]
@allergie int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
	FROM [dbo].[PersonAllergies] [personAllergies]
	WHERE [Allergie]=@allergie
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonAllergies_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
	FROM [dbo].[PersonAllergies] [personAllergies]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetBySeveritys')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetBySeveritys] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonAllergies_GetBySeveritys]
@severity int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
	FROM [dbo].[PersonAllergies] [personAllergies]
	WHERE [Severity]=@severity
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_GetBy_Status')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_GetBy_Status] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonAllergies_GetBy_Status]
@status int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personAllergies].[idPersonAllergie] AS 'idPersonAllergie',
	[personAllergies].[Allergie] AS 'Allergie',
	[personAllergies].[EndDate] AS 'EndDate',
	[personAllergies].[Obs] AS 'Obs',
	[personAllergies].[Person] AS 'Person',
	[personAllergies].[Severity] AS 'Severity',
	[personAllergies].[StartDate] AS 'StartDate',
	[personAllergies].[Status] AS 'Status'
	FROM [dbo].[PersonAllergies] [personAllergies]
	WHERE [Status]=@status
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonAllergies_Insert]
    @idPersonAllergie int OUT,
	@allergie int,
	@endDate datetime = NULL,
	@obs varchar(255) = NULL,
	@person int,
	@severity int,
	@startDate datetime = NULL,
	@status int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PersonAllergies] ([Allergie], [EndDate], [Obs], [Person], [Severity], [StartDate], [Status])
	VALUES (@allergie, @endDate, @obs, @person, @severity, @startDate, @status)
    SET @idPersonAllergie = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonAllergies_Update]
    @idPersonAllergie int,
	@allergie int,
	@endDate datetime = NULL,
	@obs varchar(255) = NULL,
	@person int,
	@severity int,
	@startDate datetime = NULL,
	@status int
AS
BEGIN

	--The [dbo].[PersonAllergies] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PersonAllergies] 
	SET [Allergie] = @allergie, [EndDate] = @endDate, [Obs] = @obs, [Person] = @person, [Severity] = @severity, [StartDate] = @startDate, [Status] = @status
	WHERE [idPersonAllergie]=@idPersonAllergie

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonAllergies_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonAllergies_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonAllergies_Delete]
	 @idPersonAllergie int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonAllergies]
	WHERE [idPersonAllergie]=@idPersonAllergie
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonConditions] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonConditions_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personConditions].[idPersonCondition] AS 'idPersonCondition',
	[personConditions].[Condition] AS 'Condition',
	[personConditions].[EndDate] AS 'EndDate',
	[personConditions].[Obs] AS 'Obs',
	[personConditions].[Person] AS 'Person',
	[personConditions].[StartDate] AS 'StartDate',
	[personConditions].[Status] AS 'Status'
FROM [dbo].[PersonConditions] [personConditions]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonConditions_GetById] 
	@idPersonCondition int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personConditions].[idPersonCondition] AS 'idPersonCondition',
	[personConditions].[Condition] AS 'Condition',
	[personConditions].[EndDate] AS 'EndDate',
	[personConditions].[Obs] AS 'Obs',
	[personConditions].[Person] AS 'Person',
	[personConditions].[StartDate] AS 'StartDate',
	[personConditions].[Status] AS 'Status'
	FROM [dbo].[PersonConditions] [personConditions]
	WHERE [idPersonCondition]=@idPersonCondition

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_GetByConditions')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_GetByConditions] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonConditions_GetByConditions]
@condition int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personConditions].[idPersonCondition] AS 'idPersonCondition',
	[personConditions].[Condition] AS 'Condition',
	[personConditions].[EndDate] AS 'EndDate',
	[personConditions].[Obs] AS 'Obs',
	[personConditions].[Person] AS 'Person',
	[personConditions].[StartDate] AS 'StartDate',
	[personConditions].[Status] AS 'Status'
	FROM [dbo].[PersonConditions] [personConditions]
	WHERE [Condition]=@condition
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonConditions_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personConditions].[idPersonCondition] AS 'idPersonCondition',
	[personConditions].[Condition] AS 'Condition',
	[personConditions].[EndDate] AS 'EndDate',
	[personConditions].[Obs] AS 'Obs',
	[personConditions].[Person] AS 'Person',
	[personConditions].[StartDate] AS 'StartDate',
	[personConditions].[Status] AS 'Status'
	FROM [dbo].[PersonConditions] [personConditions]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_GetBy_Status')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_GetBy_Status] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonConditions_GetBy_Status]
@status int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personConditions].[idPersonCondition] AS 'idPersonCondition',
	[personConditions].[Condition] AS 'Condition',
	[personConditions].[EndDate] AS 'EndDate',
	[personConditions].[Obs] AS 'Obs',
	[personConditions].[Person] AS 'Person',
	[personConditions].[StartDate] AS 'StartDate',
	[personConditions].[Status] AS 'Status'
	FROM [dbo].[PersonConditions] [personConditions]
	WHERE [Status]=@status
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonConditions_Insert]
    @idPersonCondition int OUT,
	@condition int,
	@endDate datetime = NULL,
	@obs varchar(255) = NULL,
	@person int,
	@startDate datetime = NULL,
	@status int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PersonConditions] ([Condition], [EndDate], [Obs], [Person], [StartDate], [Status])
	VALUES (@condition, @endDate, @obs, @person, @startDate, @status)
    SET @idPersonCondition = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonConditions_Update]
    @idPersonCondition int,
	@condition int,
	@endDate datetime = NULL,
	@obs varchar(255) = NULL,
	@person int,
	@startDate datetime = NULL,
	@status int
AS
BEGIN

	--The [dbo].[PersonConditions] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PersonConditions] 
	SET [Condition] = @condition, [EndDate] = @endDate, [Obs] = @obs, [Person] = @person, [StartDate] = @startDate, [Status] = @status
	WHERE [idPersonCondition]=@idPersonCondition

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonConditions_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonConditions_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonConditions_Delete]
	 @idPersonCondition int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonConditions]
	WHERE [idPersonCondition]=@idPersonCondition
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonInmunizations] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonInmunizations_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personInmunizations].[PersonInmunization] AS 'PersonInmunization',
	[personInmunizations].[Date] AS 'Date',
	[personInmunizations].[idInmunization] AS 'idInmunization',
	[personInmunizations].[Person] AS 'Person'
FROM [dbo].[PersonInmunizations] [personInmunizations]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonInmunizations_GetById] 
	@personInmunization int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personInmunizations].[PersonInmunization] AS 'PersonInmunization',
	[personInmunizations].[Date] AS 'Date',
	[personInmunizations].[idInmunization] AS 'idInmunization',
	[personInmunizations].[Person] AS 'Person'
	FROM [dbo].[PersonInmunizations] [personInmunizations]
	WHERE [PersonInmunization]=@personInmunization

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_GetByInmunizations')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_GetByInmunizations] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonInmunizations_GetByInmunizations]
@idInmunization int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personInmunizations].[PersonInmunization] AS 'PersonInmunization',
	[personInmunizations].[Date] AS 'Date',
	[personInmunizations].[idInmunization] AS 'idInmunization',
	[personInmunizations].[Person] AS 'Person'
	FROM [dbo].[PersonInmunizations] [personInmunizations]
	WHERE [idInmunization]=@idInmunization
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonInmunizations_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personInmunizations].[PersonInmunization] AS 'PersonInmunization',
	[personInmunizations].[Date] AS 'Date',
	[personInmunizations].[idInmunization] AS 'idInmunization',
	[personInmunizations].[Person] AS 'Person'
	FROM [dbo].[PersonInmunizations] [personInmunizations]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonInmunizations_Insert]
    @personInmunization int OUT,
	@date datetime = NULL,
	@idInmunization int,
	@person int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PersonInmunizations] ([Date], [idInmunization], [Person])
	VALUES (@date, @idInmunization, @person)
    SET @personInmunization = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonInmunizations_Update]
    @personInmunization int,
	@date datetime = NULL,
	@idInmunization int,
	@person int
AS
BEGIN

	--The [dbo].[PersonInmunizations] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PersonInmunizations] 
	SET [Date] = @date, [idInmunization] = @idInmunization, [Person] = @person
	WHERE [PersonInmunization]=@personInmunization

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonInmunizations_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonInmunizations_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonInmunizations_Delete]
	 @personInmunization int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonInmunizations]
	WHERE [PersonInmunization]=@personInmunization
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonMetrics] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonMetrics_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonMetrics_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonMetrics_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personMetrics].[idPersonMetric] AS 'idPersonMetric'
FROM [dbo].[PersonMetrics] [personMetrics]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonMetrics_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonMetrics_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonMetrics_GetById] 
	@idPersonMetric int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personMetrics].[idPersonMetric] AS 'idPersonMetric'
	FROM [dbo].[PersonMetrics] [personMetrics]
	WHERE [idPersonMetric]=@idPersonMetric

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonMetrics_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonMetrics_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonMetrics_Insert]
    @idPersonMetric int OUT
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    
    SET @idPersonMetric = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonMetrics_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonMetrics_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonMetrics_Update]
    @idPersonMetric int
AS
BEGIN

	--The [dbo].[PersonMetrics] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonMetrics_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonMetrics_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonMetrics_Delete]
	 @idPersonMetric int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonMetrics]
	WHERE [idPersonMetric]=@idPersonMetric
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonProcedures] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonProcedures_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personProcedures].[idPersonProcedure] AS 'idPersonProcedure',
	[personProcedures].[EndDate] AS 'EndDate',
	[personProcedures].[idProcedure] AS 'idProcedure',
	[personProcedures].[Person] AS 'Person',
	[personProcedures].[StartDate] AS 'StartDate'
FROM [dbo].[PersonProcedures] [personProcedures]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonProcedures_GetById] 
	@idPersonProcedure int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personProcedures].[idPersonProcedure] AS 'idPersonProcedure',
	[personProcedures].[EndDate] AS 'EndDate',
	[personProcedures].[idProcedure] AS 'idProcedure',
	[personProcedures].[Person] AS 'Person',
	[personProcedures].[StartDate] AS 'StartDate'
	FROM [dbo].[PersonProcedures] [personProcedures]
	WHERE [idPersonProcedure]=@idPersonProcedure

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_GetBy_Procedures')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_GetBy_Procedures] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonProcedures_GetBy_Procedures]
@idProcedure int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personProcedures].[idPersonProcedure] AS 'idPersonProcedure',
	[personProcedures].[EndDate] AS 'EndDate',
	[personProcedures].[idProcedure] AS 'idProcedure',
	[personProcedures].[Person] AS 'Person',
	[personProcedures].[StartDate] AS 'StartDate'
	FROM [dbo].[PersonProcedures] [personProcedures]
	WHERE [idProcedure]=@idProcedure
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonProcedures_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personProcedures].[idPersonProcedure] AS 'idPersonProcedure',
	[personProcedures].[EndDate] AS 'EndDate',
	[personProcedures].[idProcedure] AS 'idProcedure',
	[personProcedures].[Person] AS 'Person',
	[personProcedures].[StartDate] AS 'StartDate'
	FROM [dbo].[PersonProcedures] [personProcedures]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonProcedures_Insert]
    @idPersonProcedure int OUT,
	@endDate datetime = NULL,
	@idProcedure int,
	@person int,
	@startDate datetime = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PersonProcedures] ([EndDate], [idProcedure], [Person], [StartDate])
	VALUES (@endDate, @idProcedure, @person, @startDate)
    SET @idPersonProcedure = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonProcedures_Update]
    @idPersonProcedure int,
	@endDate datetime = NULL,
	@idProcedure int,
	@person int,
	@startDate datetime = NULL
AS
BEGIN

	--The [dbo].[PersonProcedures] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PersonProcedures] 
	SET [EndDate] = @endDate, [idProcedure] = @idProcedure, [Person] = @person, [StartDate] = @startDate
	WHERE [idPersonProcedure]=@idPersonProcedure

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonProcedures_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonProcedures_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonProcedures_Delete]
	 @idPersonProcedure int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonProcedures]
	WHERE [idPersonProcedure]=@idPersonProcedure
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Persons] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Persons_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
FROM [dbo].[Persons] [persons]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Persons_GetById] 
	@idPerson int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [idPerson]=@idPerson

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetByBloodTypes')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetByBloodTypes] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Persons_GetByBloodTypes]
@bloodType int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [BloodType]=@bloodType
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetByContactData')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetByContactData] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Persons_GetByContactData]
@idContactData int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [idContactData]=@idContactData
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetByPatients')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetByPatients] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Persons_GetByPatients]
@idPatient int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [idPatient]=@idPatient
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetByRaces')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetByRaces] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Persons_GetByRaces]
@race int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [Race]=@race
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_GetBySexs')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_GetBySexs] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[Persons_GetBySexs]
@sex int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[persons].[idPerson] AS 'idPerson',
	[persons].[BloodType] AS 'BloodType',
	[persons].[DateOfBirth] AS 'DateOfBirth',
	[persons].[idContactData] AS 'idContactData',
	[persons].[idPatient] AS 'idPatient',
	[persons].[Race] AS 'Race',
	[persons].[Sex] AS 'Sex'
	FROM [dbo].[Persons] [persons]
	WHERE [Sex]=@sex
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Persons_Insert]
    @idPerson int OUT,
	@bloodType int,
	@dateOfBirth datetime = NULL,
	@idContactData int,
	@idPatient int,
	@race int,
	@sex int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Persons] ([BloodType], [DateOfBirth], [idContactData], [idPatient], [Race], [Sex])
	VALUES (@bloodType, @dateOfBirth, @idContactData, @idPatient, @race, @sex)
    SET @idPerson = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Persons_Update]
    @idPerson int,
	@bloodType int,
	@dateOfBirth datetime = NULL,
	@idContactData int,
	@idPatient int,
	@race int,
	@sex int
AS
BEGIN

	--The [dbo].[Persons] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Persons] 
	SET [BloodType] = @bloodType, [DateOfBirth] = @dateOfBirth, [idContactData] = @idContactData, [idPatient] = @idPatient, [Race] = @race, [Sex] = @sex
	WHERE [idPerson]=@idPerson

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Persons_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Persons_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Persons_Delete]
	 @idPerson int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Persons]
	WHERE [idPerson]=@idPerson
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PersonTestResult] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonTestResult_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[personTestResult].[idPersonTestRes] AS 'idPersonTestRes',
	[personTestResult].[Date] AS 'Date',
	[personTestResult].[Person] AS 'Person',
	[personTestResult].[TestResult] AS 'TestResult',
	[personTestResult].[Units] AS 'Units',
	[personTestResult].[Value] AS 'Value'
FROM [dbo].[PersonTestResult] [personTestResult]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonTestResult_GetById] 
	@idPersonTestRes int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[personTestResult].[idPersonTestRes] AS 'idPersonTestRes',
	[personTestResult].[Date] AS 'Date',
	[personTestResult].[Person] AS 'Person',
	[personTestResult].[TestResult] AS 'TestResult',
	[personTestResult].[Units] AS 'Units',
	[personTestResult].[Value] AS 'Value'
	FROM [dbo].[PersonTestResult] [personTestResult]
	WHERE [idPersonTestRes]=@idPersonTestRes

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonTestResult_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personTestResult].[idPersonTestRes] AS 'idPersonTestRes',
	[personTestResult].[Date] AS 'Date',
	[personTestResult].[Person] AS 'Person',
	[personTestResult].[TestResult] AS 'TestResult',
	[personTestResult].[Units] AS 'Units',
	[personTestResult].[Value] AS 'Value'
	FROM [dbo].[PersonTestResult] [personTestResult]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_GetByTestResults')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_GetByTestResults] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PersonTestResult_GetByTestResults]
@testResult int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[personTestResult].[idPersonTestRes] AS 'idPersonTestRes',
	[personTestResult].[Date] AS 'Date',
	[personTestResult].[Person] AS 'Person',
	[personTestResult].[TestResult] AS 'TestResult',
	[personTestResult].[Units] AS 'Units',
	[personTestResult].[Value] AS 'Value'
	FROM [dbo].[PersonTestResult] [personTestResult]
	WHERE [TestResult]=@testResult
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonTestResult_Insert]
    @idPersonTestRes int OUT,
	@date datetime = NULL,
	@person int,
	@testResult int,
	@units char(20) = NULL,
	@value decimal(18,0) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PersonTestResult] ([Date], [Person], [TestResult], [Units], [Value])
	VALUES (@date, @person, @testResult, @units, @value)
    SET @idPersonTestRes = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonTestResult_Update]
    @idPersonTestRes int,
	@date datetime = NULL,
	@person int,
	@testResult int,
	@units char(20) = NULL,
	@value decimal(18,0) = NULL
AS
BEGIN

	--The [dbo].[PersonTestResult] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PersonTestResult] 
	SET [Date] = @date, [Person] = @person, [TestResult] = @testResult, [Units] = @units, [Value] = @value
	WHERE [idPersonTestRes]=@idPersonTestRes

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PersonTestResult_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PersonTestResult_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PersonTestResult_Delete]
	 @idPersonTestRes int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PersonTestResult]
	WHERE [idPersonTestRes]=@idPersonTestRes
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[PesonMedications] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PesonMedications_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[pesonMedications].[idPersonMedication] AS 'idPersonMedication',
	[pesonMedications].[EndDate] AS 'EndDate',
	[pesonMedications].[HowMany] AS 'HowMany',
	[pesonMedications].[HowOften] AS 'HowOften',
	[pesonMedications].[Medication] AS 'Medication',
	[pesonMedications].[Obs] AS 'Obs',
	[pesonMedications].[Person] AS 'Person',
	[pesonMedications].[StarDate] AS 'StarDate',
	[pesonMedications].[Status] AS 'Status'
FROM [dbo].[PesonMedications] [pesonMedications]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PesonMedications_GetById] 
	@idPersonMedication int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[pesonMedications].[idPersonMedication] AS 'idPersonMedication',
	[pesonMedications].[EndDate] AS 'EndDate',
	[pesonMedications].[HowMany] AS 'HowMany',
	[pesonMedications].[HowOften] AS 'HowOften',
	[pesonMedications].[Medication] AS 'Medication',
	[pesonMedications].[Obs] AS 'Obs',
	[pesonMedications].[Person] AS 'Person',
	[pesonMedications].[StarDate] AS 'StarDate',
	[pesonMedications].[Status] AS 'Status'
	FROM [dbo].[PesonMedications] [pesonMedications]
	WHERE [idPersonMedication]=@idPersonMedication

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_GetByMedications')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_GetByMedications] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PesonMedications_GetByMedications]
@medication int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[pesonMedications].[idPersonMedication] AS 'idPersonMedication',
	[pesonMedications].[EndDate] AS 'EndDate',
	[pesonMedications].[HowMany] AS 'HowMany',
	[pesonMedications].[HowOften] AS 'HowOften',
	[pesonMedications].[Medication] AS 'Medication',
	[pesonMedications].[Obs] AS 'Obs',
	[pesonMedications].[Person] AS 'Person',
	[pesonMedications].[StarDate] AS 'StarDate',
	[pesonMedications].[Status] AS 'Status'
	FROM [dbo].[PesonMedications] [pesonMedications]
	WHERE [Medication]=@medication
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_GetByPersons')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_GetByPersons] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PesonMedications_GetByPersons]
@person int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[pesonMedications].[idPersonMedication] AS 'idPersonMedication',
	[pesonMedications].[EndDate] AS 'EndDate',
	[pesonMedications].[HowMany] AS 'HowMany',
	[pesonMedications].[HowOften] AS 'HowOften',
	[pesonMedications].[Medication] AS 'Medication',
	[pesonMedications].[Obs] AS 'Obs',
	[pesonMedications].[Person] AS 'Person',
	[pesonMedications].[StarDate] AS 'StarDate',
	[pesonMedications].[Status] AS 'Status'
	FROM [dbo].[PesonMedications] [pesonMedications]
	WHERE [Person]=@person
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_GetBy_Status')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_GetBy_Status] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[PesonMedications_GetBy_Status]
@status int
AS
BEGIN
SET NOCOUNT ON
SELECT
	[pesonMedications].[idPersonMedication] AS 'idPersonMedication',
	[pesonMedications].[EndDate] AS 'EndDate',
	[pesonMedications].[HowMany] AS 'HowMany',
	[pesonMedications].[HowOften] AS 'HowOften',
	[pesonMedications].[Medication] AS 'Medication',
	[pesonMedications].[Obs] AS 'Obs',
	[pesonMedications].[Person] AS 'Person',
	[pesonMedications].[StarDate] AS 'StarDate',
	[pesonMedications].[Status] AS 'Status'
	FROM [dbo].[PesonMedications] [pesonMedications]
	WHERE [Status]=@status
SET NOCOUNT OFF
END
GO


IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PesonMedications_Insert]
    @idPersonMedication int OUT,
	@endDate datetime = NULL,
	@howMany varchar(56) = NULL,
	@howOften varchar(56) = NULL,
	@medication int,
	@obs varchar(255) = NULL,
	@person int,
	@starDate datetime = NULL,
	@status int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PesonMedications] ([EndDate], [HowMany], [HowOften], [Medication], [Obs], [Person], [StarDate], [Status])
	VALUES (@endDate, @howMany, @howOften, @medication, @obs, @person, @starDate, @status)
    SET @idPersonMedication = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PesonMedications_Update]
    @idPersonMedication int,
	@endDate datetime = NULL,
	@howMany varchar(56) = NULL,
	@howOften varchar(56) = NULL,
	@medication int,
	@obs varchar(255) = NULL,
	@person int,
	@starDate datetime = NULL,
	@status int
AS
BEGIN

	--The [dbo].[PesonMedications] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PesonMedications] 
	SET [EndDate] = @endDate, [HowMany] = @howMany, [HowOften] = @howOften, [Medication] = @medication, [Obs] = @obs, [Person] = @person, [StarDate] = @starDate, [Status] = @status
	WHERE [idPersonMedication]=@idPersonMedication

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'PesonMedications_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[PesonMedications_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[PesonMedications_Delete]
	 @idPersonMedication int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PesonMedications]
	WHERE [idPersonMedication]=@idPersonMedication
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Races] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Races_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Races_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Races_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[races].[idRace] AS 'idRace',
	[races].[Name] AS 'Name'
FROM [dbo].[Races] [races]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Races_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Races_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Races_GetById] 
	@idRace int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[races].[idRace] AS 'idRace',
	[races].[Name] AS 'Name'
	FROM [dbo].[Races] [races]
	WHERE [idRace]=@idRace

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Races_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Races_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Races_Insert]
    @idRace int OUT,
	@name varchar(50) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Races] ([Name])
	VALUES (@name)
    SET @idRace = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Races_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Races_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Races_Update]
    @idRace int,
	@name varchar(50) = NULL
AS
BEGIN

	--The [dbo].[Races] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Races] 
	SET [Name] = @name
	WHERE [idRace]=@idRace

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Races_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Races_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Races_Delete]
	 @idRace int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Races]
	WHERE [idRace]=@idRace
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Severitys] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Severitys_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Severitys_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Severitys_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[severitys].[idSeverity] AS 'idSeverity',
	[severitys].[Name] AS 'Name'
FROM [dbo].[Severitys] [severitys]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Severitys_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Severitys_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Severitys_GetById] 
	@idSeverity int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[severitys].[idSeverity] AS 'idSeverity',
	[severitys].[Name] AS 'Name'
	FROM [dbo].[Severitys] [severitys]
	WHERE [idSeverity]=@idSeverity

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Severitys_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Severitys_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Severitys_Insert]
    @idSeverity int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Severitys] ([Name])
	VALUES (@name)
    SET @idSeverity = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Severitys_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Severitys_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Severitys_Update]
    @idSeverity int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[Severitys] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Severitys] 
	SET [Name] = @name
	WHERE [idSeverity]=@idSeverity

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Severitys_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Severitys_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Severitys_Delete]
	 @idSeverity int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Severitys]
	WHERE [idSeverity]=@idSeverity
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Sexs] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Sexs_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Sexs_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Sexs_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[sexs].[idSex] AS 'idSex',
	[sexs].[Name] AS 'Name'
FROM [dbo].[Sexs] [sexs]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Sexs_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Sexs_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Sexs_GetById] 
	@idSex int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[sexs].[idSex] AS 'idSex',
	[sexs].[Name] AS 'Name'
	FROM [dbo].[Sexs] [sexs]
	WHERE [idSex]=@idSex

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Sexs_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Sexs_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Sexs_Insert]
    @idSex int OUT,
	@name char(20) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Sexs] ([Name])
	VALUES (@name)
    SET @idSex = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Sexs_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Sexs_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Sexs_Update]
    @idSex int,
	@name char(20) = NULL
AS
BEGIN

	--The [dbo].[Sexs] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Sexs] 
	SET [Name] = @name
	WHERE [idSex]=@idSex

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Sexs_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Sexs_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Sexs_Delete]
	 @idSex int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Sexs]
	WHERE [idSex]=@idSex
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[TestResults] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'TestResults_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[TestResults_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[TestResults_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[testResults].[idTestResult] AS 'idTestResult',
	[testResults].[Name] AS 'Name'
FROM [dbo].[TestResults] [testResults]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'TestResults_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[TestResults_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[TestResults_GetById] 
	@idTestResult int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[testResults].[idTestResult] AS 'idTestResult',
	[testResults].[Name] AS 'Name'
	FROM [dbo].[TestResults] [testResults]
	WHERE [idTestResult]=@idTestResult

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'TestResults_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[TestResults_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[TestResults_Insert]
    @idTestResult int OUT,
	@name varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[TestResults] ([Name])
	VALUES (@name)
    SET @idTestResult = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'TestResults_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[TestResults_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[TestResults_Update]
    @idTestResult int,
	@name varchar(255) = NULL
AS
BEGIN

	--The [dbo].[TestResults] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[TestResults] 
	SET [Name] = @name
	WHERE [idTestResult]=@idTestResult

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'TestResults_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[TestResults_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[TestResults_Delete]
	 @idTestResult int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[TestResults]
	WHERE [idTestResult]=@idTestResult
    
    SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Users] Table
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Users_GetAll')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Users_GetAll] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Users_GetAll]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[users].[idUser] AS 'idUser',
	[users].[Login] AS 'Login',
	[users].[UserPassword] AS 'UserPassword'
FROM [dbo].[Users] [users]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Users_GetById')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Users_GetById] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Users_GetById] 
	@idUser int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[users].[idUser] AS 'idUser',
	[users].[Login] AS 'Login',
	[users].[UserPassword] AS 'UserPassword'
	FROM [dbo].[Users] [users]
	WHERE [idUser]=@idUser

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Users_Insert')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Users_Insert] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Users_Insert]
    @idUser int OUT,
	@login char(15) = NULL,
	@userPassword char(15) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Users] ([Login], [UserPassword])
	VALUES (@login, @userPassword)
    SET @idUser = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Users_Update')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Users_Update] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Users_Update]
    @idUser int,
	@login char(15) = NULL,
	@userPassword char(15) = NULL
AS
BEGIN

	--The [dbo].[Users] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Users] 
	SET [Login] = @login, [UserPassword] = @userPassword
	WHERE [idUser]=@idUser

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Users_Delete')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Users_Delete] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Users_Delete]
	 @idUser int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Users]
	WHERE [idUser]=@idUser
    
    SET NOCOUNT OFF
END

GO

